{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import xlwings as xw\n",
    "import pandas as pd\n",
    "from pandas import DataFrame\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "app = xw.App(visible=False, add_book=False)\n",
    "xb = app.books.open(r'F:\\文档\\森马\\汇总整理.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 9999 entries, 0 to 9998\n",
      "Data columns (total 18 columns):\n",
      "辅料编号    315 non-null object\n",
      "物料名称    482 non-null object\n",
      "第1套色    326 non-null object\n",
      "None    231 non-null object\n",
      "第2套色    226 non-null object\n",
      "None    222 non-null object\n",
      "第3套色    176 non-null object\n",
      "None    164 non-null object\n",
      "第4套色    153 non-null object\n",
      "None    129 non-null object\n",
      "第5套色    95 non-null object\n",
      "None    70 non-null object\n",
      "规格      275 non-null object\n",
      "用量      323 non-null object\n",
      "单位      369 non-null object\n",
      "款号      651 non-null object\n",
      "None    120 non-null object\n",
      "None    205 non-null float64\n",
      "dtypes: float64(1), object(17)\n",
      "memory usage: 1.4+ MB\n"
     ]
    }
   ],
   "source": [
    "df = xb.sheets[0].range('a1:aa10000').options(DataFrame).value\n",
    "df.reset_index(inplace=True)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 801 entries, 0 to 800\n",
      "Data columns (total 18 columns):\n",
      "辅料编号    315 non-null object\n",
      "物料名称    482 non-null object\n",
      "第1套色    326 non-null object\n",
      "None    231 non-null object\n",
      "第2套色    226 non-null object\n",
      "None    222 non-null object\n",
      "第3套色    176 non-null object\n",
      "None    164 non-null object\n",
      "第4套色    153 non-null object\n",
      "None    129 non-null object\n",
      "第5套色    95 non-null object\n",
      "None    70 non-null object\n",
      "规格      275 non-null object\n",
      "用量      323 non-null object\n",
      "单位      369 non-null object\n",
      "款号      651 non-null object\n",
      "None    120 non-null object\n",
      "None    205 non-null float64\n",
      "dtypes: float64(1), object(17)\n",
      "memory usage: 118.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df.dropna(axis=0, how='all', inplace=True)\n",
    "df.dropna(axis=1, how='all', inplace=True)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda\\lib\\site-packages\\ipykernel\\__main__.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  from ipykernel import kernelapp as app\n"
     ]
    }
   ],
   "source": [
    "new = df.iloc[:,[0,1,-5, -4, -3, -2, -1]]\n",
    "new.dropna(axis=0, how='all', inplace=True)\n",
    "last = new.replace({np.nan:'0000', None:'0000'})\n",
    "last.columns = ['辅料编号','物料名称','用量', '单位', '款号1', '款号2', '款号']\n",
    "s = last['款号']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "last.loc[(last['款号'] == '0000'), ['款号']] = "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      1\n",
       "1      1\n",
       "2      1\n",
       "3      1\n",
       "4      1\n",
       "5      1\n",
       "6      1\n",
       "7      1\n",
       "8      1\n",
       "9      1\n",
       "10     1\n",
       "11     1\n",
       "12     1\n",
       "13     1\n",
       "14     1\n",
       "15     1\n",
       "16     1\n",
       "17     1\n",
       "18     1\n",
       "19     1\n",
       "20     1\n",
       "21     1\n",
       "22     1\n",
       "23     1\n",
       "24     1\n",
       "25     1\n",
       "26     1\n",
       "27     1\n",
       "28     1\n",
       "29     1\n",
       "      ..\n",
       "771    1\n",
       "772    1\n",
       "773    1\n",
       "774    1\n",
       "775    1\n",
       "776    1\n",
       "777    1\n",
       "778    1\n",
       "779    1\n",
       "780    1\n",
       "781    1\n",
       "782    1\n",
       "783    1\n",
       "784    1\n",
       "785    1\n",
       "786    1\n",
       "787    1\n",
       "788    1\n",
       "789    1\n",
       "790    1\n",
       "791    1\n",
       "792    1\n",
       "793    1\n",
       "794    1\n",
       "795    1\n",
       "796    1\n",
       "797    1\n",
       "798    1\n",
       "799    1\n",
       "800    1\n",
       "Name: 款号, dtype: object"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "last['款号']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
